#!/usr/bin/env python3
# -*- coding: UTF-8 -*-
"""
@author: caijj
@version: V1.0
@file: zg_sz_new_house_info.py
@time: 2022/02/11
"""
import sys

sys.path.append('E:\python_project')
import csv
import multiprocessing
import os
import random
import re
import uuid
from time import sleep
import pandas as pd
import requests
# from fake_useragent import UserAgent
from lxml import etree
from selenium import webdriver
from selenium.webdriver.firefox.options import Options
from sqlalchemy import create_engine
from sqlalchemy.exc import ResourceClosedError
from common.globalvar import GlobalVar
from config.conf import cm
from tools import timeutil, image_orc
from tools.database_tool import DataBaseTool
from tools.decorator import update_flag
from tools.DingTalk_Base import DingTalk_Disaster

ding = DingTalk_Disaster()
xf_table_root_directory = 'mega_{city}_xf_timely_deal_info'
xf_file_root_directory = 'mega_{city}_xf_timely_deal_info.csv'


class InsertHouseTimelyDealInfo():

    @staticmethod
    def firefox_enter_target_page(url='https://www.creprice.cn/urban/su.html'):  # firefox进入目标页面
        firefox_options = Options()
        firefox_options.headless = True
        web_driver = webdriver.Firefox(options=firefox_options)
        web_driver.maximize_window()
        web_driver.get(url)
        return web_driver

    @staticmethod
    def chrome_enter_target_page(url):  # chrome进入目标页面
        chrome_options = webdriver.ChromeOptions()
        chrome_options.add_argument('--headless')
        chrome_options.add_argument('--disable-gpu')
        web_driver = webdriver.Chrome(options=chrome_options)
        web_driver.maximize_window()
        web_driver.get(url)
        return web_driver

    @staticmethod
    def quit(web_driver):  # 退出
        if web_driver is not None:
            web_driver.quit()
            sleep(5)

    @staticmethod
    def get_table_content(tree, table_id, index=0):
        # 定位获取表格信息
        tb = tree.xpath(table_id)
        # 将byte类型解码为str类型
        tb = etree.tostring(tb[index], encoding='utf8').decode()
        return tb

    """
    上海-实时成交数据
    """

    def sh_get_timely_deal_info(self, create_time, file_name, table_name,
                                url='http://www.fangdi.com.cn/new_house/new_house_district_bargain.html'):
        df = pd.DataFrame(columns=['id', 'stat_date', 'city_region', 'total_deal_count', 'total_deal_area_count',
                                   'residence_deal_count', 'residence_deal_area_count',
                                   'create_time'])
        df['city_region'] = ['嘉定区', '普陀区', '杨浦区', '闸北区', '长宁区', '卢湾区', '徐汇区', '松江区', '南汇区', '黄浦区', '青浦区', '奉贤区', '崇明区',
                             '金山区', '闵行区', '浦东新区', '虹口区', '静安区']
        df['total_deal_count'] = 0
        df['total_deal_area_count'] = 0
        df['residence_deal_count'] = 0
        df['residence_deal_area_count'] = 0
        df['create_time'] = create_time
        df['id'] = df.apply(lambda _: uuid.uuid4(), axis=1)
        df['stat_date'] = create_time

        self.insert_timely_deal_info(df, table_name)

    """
    常州-实时成交数据
    """

    def changzhou_get_timely_deal_info(self, create_time, file_name=None, table_name=None,
                                       url='http://gs.czfdc.com.cn/newxgs/index.aspx', index=0,
                                       residence_deal_count_list=[],
                                       residence_deal_area_count_list=[]
                                       ):
        df = pd.DataFrame(columns=['id', 'stat_date', 'city_region', 'total_deal_count', 'total_deal_area_count',
                                   'residence_deal_count', 'residence_deal_area_count',
                                   'create_time'])
        df['city_region'] = ['钟楼区', '新北区', '金坛区', '溧阳市', '经开区', '武进区', '天宁区']
        df['total_deal_count'] = 0
        df['total_deal_area_count'] = 0
        df['residence_deal_count'] = 0
        df['residence_deal_area_count'] = 0
        df['create_time'] = create_time
        df['id'] = df.apply(lambda _: uuid.uuid4(), axis=1)
        df['stat_date'] = create_time

        self.insert_timely_deal_info(df, table_name)

    """
    杭州-实时成交数据
    """

    def hz_get_timely_deal_info(self, create_time, file_name, table_name,
                                url='http://fgj.hangzhou.gov.cn/col/col1229440802/index.html'):
        df = pd.DataFrame(columns=['id', 'stat_date', 'city_region', 'total_deal_count', 'total_deal_area_count',
                                   'residence_deal_count', 'residence_deal_area_count',
                                   'create_time'])
        df['city_region'] = ['之江', '桐庐', '余杭', '下城', '拱墅', '西湖', '富阳', '江干', '钱塘新区', '滨江', '建德', '杭州', '淳安', '上城', '萧山',
                             '临安']
        df['total_deal_count'] = 0
        df['total_deal_area_count'] = 0
        df['residence_deal_count'] = 0
        df['residence_deal_area_count'] = 0
        df['create_time'] = create_time
        df['id'] = df.apply(lambda _: uuid.uuid4(), axis=1)
        df['stat_date'] = create_time

        self.insert_timely_deal_info(df, table_name)

    """
    南京-实时成交数据
    """

    def nj_get_timely_deal_info(self, create_time, file_name, table_name,
                                url='https://www.njhouse.com.cn/data/index'):
        df = pd.DataFrame(columns=['id', 'stat_date', 'city_region', 'total_deal_count', 'total_deal_area_count',
                                   'residence_deal_count', 'residence_deal_area_count',
                                   'create_time'])
        df['city_region'] = ['玄武', '鼓楼', '六合', '溧水', '高淳', '南京', '江宁', '雨花台', '秦淮', '栖霞', '浦口', '建邺']
        df['total_deal_count'] = 0
        df['total_deal_area_count'] = 0
        df['residence_deal_count'] = 0
        df['residence_deal_area_count'] = 0
        df['create_time'] = create_time
        df['id'] = df.apply(lambda _: uuid.uuid4(), axis=1)
        df['stat_date'] = create_time

        self.insert_timely_deal_info(df, table_name)

    """
    青岛-实时成交数据
    """

    def qd_get_timely_deal_info(self, create_time, file_name, table_name,
                                url='https://www.qdfd.com.cn/qdweb/realweb/indexnew.jsp'):
        df = pd.DataFrame(columns=['id', 'stat_date', 'city_region', 'total_deal_count', 'total_deal_area_count',
                                   'residence_deal_count', 'residence_deal_area_count',
                                   'create_time'])
        df['city_region'] = ['莱西市', '前湾保税港区', '高新区', '黄岛区', '市南区', '胶州市', '崂山区', '平度市', '青岛', '城阳区', '市北区', '即墨区',
                             '李沧区']
        df['total_deal_count'] = 0
        df['total_deal_area_count'] = 0
        df['residence_deal_count'] = 0
        df['residence_deal_area_count'] = 0
        df['create_time'] = create_time
        df['id'] = df.apply(lambda _: uuid.uuid4(), axis=1)
        df['stat_date'] = create_time

        self.insert_timely_deal_info(df, table_name)

    """
    湖州-实时成交数据
    """

    # def huzhou_get_timely_deal_info(self, create_time, file_name, table_name,
    #                                 url='http://hufdc.jsj.huzhou.gov.cn/'):
    #     web_driver = self.chrome_enter_target_page(url)
    #     sleep(2)
    #     html_source = web_driver.page_source  # 该属性可以获取当前浏览器的当前页的源码（html）
    #     tree = etree.HTML(html_source)
    #     try:
    #         xf_tb = self.get_table_content(tree,
    #                                        "//div[contains(text(),'新房交易信息')]/following-sibling::div["
    #                                        "@class='box15']//table",
    #                                        1)
    #         es_tb = self.get_table_content(tree,
    #                                        "//div[contains(text(),'二手房信息')]/following-sibling::div["
    #                                        "@class='box15']//table",
    #                                        1)
    #         if '暂无最新数据' in xf_tb and '暂无最新数据' in es_tb:
    #             pass
    #         elif '暂无最新数据' in xf_tb and '暂无最新数据' not in es_tb:
    #             es_df = pd.read_html(es_tb, encoding='utf-8', header=0)[0].fillna(0)
    #         elif '暂无最新数据' in es_tb and '暂无最新数据' not in xf_tb:
    #             xf_df = pd.read_html(xf_tb, encoding='utf-8', header=0)[0].fillna(0)
    #         else:
    #             xf_df = pd.read_html(xf_tb, encoding='utf-8', header=0)[0].fillna(0)
    #             es_df = pd.read_html(es_tb, encoding='utf-8', header=0)[0].fillna(0)
    #         deal_info_df = pd.DataFrame(
    #             columns=['id', 'stat_date', 'city_region', 'total_deal_count', 'total_deal_area_count',
    #                      'residence_deal_count', 'residence_deal_area_count',
    #                      'create_time'])
    #         xf_city_region = xf_df['区 域'].values.tolist()[:-1]
    #         xf_total_deal_count = xf_df['已售套数'].values.tolist()[:-1]
    #         xf_total_deal_count = [count[:-1] for count in xf_total_deal_count]
    #         xf_total_deal_area_count = xf_df['已售面积'].values.tolist()[:-1]
    #         xf_total_deal_area_count = [area.split('㎡')[0] for area in xf_total_deal_area_count]
    #         xf_deal_count_dict = dict(zip(xf_city_region, xf_total_deal_count))
    #         xf_deal_area_dict = dict(zip(xf_city_region, xf_total_deal_area_count))
    #         es_city_region = es_df['城区'].values.tolist()[:-1]
    #         es_total_deal_count = es_df['套数'].values.tolist()[:-1]
    #         es_total_deal_count = [count[:-1] for count in es_total_deal_count]
    #         es_total_deal_area_count = es_df['面积'].values.tolist()[:-1]
    #         es_total_deal_area_count = [area.split('㎡')[0] for area in es_total_deal_area_count]
    #         es_deal_count_dict = dict(zip(es_city_region, es_total_deal_count))
    #         es_deal_area_dict = dict(zip(es_city_region, es_total_deal_area_count))
    #
    #         new_es_city_region = [es_city for es_city in es_city_region if es_city not in xf_city_region]
    #         city_region = xf_city_region + new_es_city_region
    #         residence_deal_count_list, residence_deal_area_count_list = [], []
    #         for region in city_region:
    #             if region in xf_city_region and region in es_city_region:
    #                 xf_deal_count = xf_deal_count_dict[region]
    #                 xf_deal_area = xf_deal_area_dict[region].replace(',', '')
    #                 es_deal_count = es_deal_count_dict[region]
    #                 es_deal_area = es_deal_area_dict[region].replace(',', '')
    #             elif region not in xf_city_region:
    #                 xf_deal_count, xf_deal_area = 0, 0
    #                 es_deal_count = es_deal_count_dict[region]
    #                 es_deal_area = es_deal_area_dict[region].replace(',', '')
    #             else:
    #                 xf_deal_count = xf_deal_count_dict[region]
    #                 xf_deal_area = xf_deal_area_dict[region].replace(',', '')
    #                 es_deal_count, es_deal_area = 0, 0
    #             residence_deal_count_list.append(int(xf_deal_count) + int(es_deal_count))
    #             residence_deal_area_count_list.append(float(xf_deal_area) + float(es_deal_area))
    #
    #         deal_info_df['city_region'] = city_region
    #         deal_info_df['total_deal_count'] = residence_deal_count_list
    #         deal_info_df['total_deal_area_count'] = residence_deal_area_count_list
    #         deal_info_df['residence_deal_count'] = residence_deal_count_list
    #         deal_info_df['residence_deal_area_count'] = residence_deal_area_count_list
    #         deal_info_df['create_time'] = create_time
    #         deal_info_df['id'] = deal_info_df.apply(lambda _: uuid.uuid4(), axis=1)
    #         deal_info_df['stat_date'] = create_time
    #         deal_info_df.replace('全市', '青岛', inplace=True)
    #         self.check_file_is_exists(file_name)
    #         # 保存为csv格式
    #         deal_info_df.to_csv(file_name, mode="a",
    #                             encoding="utf-8",
    #                             header=False,
    #                             index=False)
    #         self.insert_timely_deal_info(file_name, table_name)
    #     except Exception as e:
    #         print(e)
    #         self.quit(web_driver)

    """
    宁波-实时成交数据
    """

    def nb_get_timely_deal_info(self, create_time, file_name, table_name,
                                url='https://www.qdfd.com.cn/qdweb/realweb/indexnew.jsp'):
        df = pd.DataFrame(columns=['id', 'stat_date', 'city_region', 'total_deal_count', 'total_deal_area_count',
                                   'residence_deal_count', 'residence_deal_area_count',
                                   'create_time'])
        df['city_region'] = ['鄞州区', '大榭', '海曙区', '镇海区', '象山县', '杭州湾', '保税区', '江北区', '梅山', '慈溪市', '余姚市', '东钱湖', '北仑区',
                             '宁海县', '奉化区', '高新区']
        df['total_deal_count'] = 0
        df['total_deal_area_count'] = 0
        df['residence_deal_count'] = 0
        df['residence_deal_area_count'] = 0
        df['create_time'] = create_time
        df['id'] = df.apply(lambda _: uuid.uuid4(), axis=1)
        df['stat_date'] = create_time
        self.insert_timely_deal_info(df, table_name)

    @staticmethod
    @update_flag()
    def insert_timely_deal_info(df, table_name):
        engine = create_engine('mssql+pymssql://base:base@192.168.106.21:1433/JRZF_BASE?charset=utf8')
        try:
            df.to_sql(table_name, engine, index=False, if_exists='fail')
        except ValueError as e:
            df.to_sql(table_name, engine, index=False, if_exists='append')


if __name__ == '__main__':
    time = timeutil.dt_strptime("%Y-%m-%d %H%M%S", )
    city_list = ['sh', 'changzhou', 'hz', 'nj', 'qd', 'nb']
    # city_list = ['nb']
    for city in city_list:
        timely_deal_info_file_name = xf_file_root_directory.format(city=city)
        timely_deal_info_table_name = xf_table_root_directory.format(city=city)
        # InsertHouseTimelyDealInfo().eval('%s_get_timely_deal_info' % city)(time, cm.tmp_file_path(
        #                                                                    timely_deal_info_file_name),
        #                                                                    timely_deal_info_table_name)
        if city == 'sh':
            InsertHouseTimelyDealInfo().sh_get_timely_deal_info(time, cm.tmp_file_path(timely_deal_info_file_name),
                                                                timely_deal_info_table_name)
        elif city == 'changzhou':
            InsertHouseTimelyDealInfo().changzhou_get_timely_deal_info(time,
                                                                       cm.tmp_file_path(timely_deal_info_file_name),
                                                                       timely_deal_info_table_name)
        elif city == 'hz':
            InsertHouseTimelyDealInfo().hz_get_timely_deal_info(time, cm.tmp_file_path(timely_deal_info_file_name),
                                                                timely_deal_info_table_name)
        elif city == 'nj':
            InsertHouseTimelyDealInfo().nj_get_timely_deal_info(time, cm.tmp_file_path(timely_deal_info_file_name),
                                                                timely_deal_info_table_name)
        elif city == 'nb':
            InsertHouseTimelyDealInfo().nb_get_timely_deal_info(time, cm.tmp_file_path(timely_deal_info_file_name),
                                                                timely_deal_info_table_name)
        else:
            InsertHouseTimelyDealInfo().qd_get_timely_deal_info(time, cm.tmp_file_path(timely_deal_info_file_name),
                                                                timely_deal_info_table_name)
